package Utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;



public class JDBCHelper {
    public static Connection con;
    public static PreparedStatement pst;
    private static final String SEP = ", ";
    private static ResultSet resultset;

    public JDBCHelper() {
        Connect();
    }



    private static void Connect()
    {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection("jdbc:sqlserver://localhost:1280;database=LoanManagerSystem",
                    "sa", "123");
            if (con != null) {
                System.out.print("Connect Successfull");
                     }
        } catch (SQLException ex) {
            Logger.getLogger(JDBCHelper.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
            System.exit(1);
        }
        
    }


     public static ResultSet excuteSelect(String sql)
     {
        JDBCHelper.Connect();
        try {
                pst = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                resultset = pst.executeQuery();
                //this.closeConnect();
            } catch (SQLException ex) {
                Logger.getLogger(JDBCHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
         return resultset;
    }

  
    public static boolean executeInsert(String tableName,ArrayList<String> columnNames,
                                        ArrayList<String> values)
    {
        try {
            JDBCHelper.Connect();
            String sql = JDBCHelper.buildInsertStatement(tableName,columnNames,values);
            pst = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pst.executeUpdate();
            JDBCHelper.closeConnect();
        } catch (SQLException ex) {
            Logger.getLogger(JDBCHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;

    }


    public static Boolean excuteInsertPlaceHolder(String tableName, int length, Object[] str)
    {

        try {
            String sql = "Insert into " + tableName + " values (" + preparePlaceHolders(length) + ")";
           // System.out.println(sql);
            pst = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                   ResultSet.CONCUR_READ_ONLY);
            
            setValues(pst, str);
             pst.executeUpdate();
            return true;

        } catch (SQLException ex) {
            Logger.getLogger(JDBCHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public static boolean executeUpdate(String tableName,ArrayList<String> columnNames,
             ArrayList<String> values,String columnID,String columnValue)
    {
        try {
                JDBCHelper.Connect();
                String sql = JDBCHelper.buildUpdateStatement(tableName,columnNames,values);
                sql = sql + " where " + columnID + " = " + "'" + columnValue + "'";
               // System.out.print(sql);
                pst = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                pst.executeUpdate();
                JDBCHelper.closeConnect();
                return true;
        } catch (SQLException ex) {
            Logger.getLogger(JDBCHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;

    }
   
    public static Boolean excuteDelete(String tableName, String column, String value)
    {
        try {
                JDBCHelper.Connect();
                String sql = "Delete from " + tableName + " where " + column + " = " + value;
                //System.out.println(sql);
                pst = con.prepareStatement(sql);
                pst.executeUpdate();
                JDBCHelper.closeConnect();
                return true;
            } 
        catch (SQLException ex)
        {
            Logger.getLogger(JDBCHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

     public static int excuteQueryReturnInt(String column ,String table,String value)
     {
        int id = 0;
        try {
            String sql = "Select " + column + " from " + table + " where " + column + " =" +value;
            System.out.println(sql);
            pst = con.prepareStatement(sql);
            resultset = pst.executeQuery();
            if (resultset.next())
            {
                id = resultset.getRow();
            }
        } catch (SQLException ex) {
            Logger.getLogger(JDBCHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return id;
    }

    public boolean executeBatchSQLQuery(ArrayList sql)
    {
        try {
            con.setAutoCommit(false);
            int i = 0;
            while (i != sql.size()) {
                pst.addBatch(sql.get(i).toString());
                i++;
            }
            pst.executeBatch();
            con.commit();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(JDBCHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

 
    public static void closeConnect(){
        try {
            pst.close();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(JDBCHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
    }


    private static String buildInsertStatement(String tableName,ArrayList<String> columnNames,
                                        ArrayList<String> values)
    {
        final StringBuilder sb= null;
        sb.append("INTO ");
        sb.append(tableName).append(" (");
        sb.append(StringUtility.join(columnNames, SEP));
        sb.append(") VALUES (");

        final List<String> insertedValues = new ArrayList<String>();
        if (values != null)
        {
            for (final String value : values)
            {
                insertedValues.add(value);
            }
        }
        while (insertedValues.size() < columnNames.size())
        {
            insertedValues.add("?");
        }
        sb.append(StringUtility.join(insertedValues, SEP));
        sb.append(")");
        return sb.toString();
    }


    private static String buildUpdateStatement(String tableName,ArrayList<String> columnNames,
                                        ArrayList<String> values)
    {
        String sb = " Update ";
        sb = sb + tableName + " SET ";
        final List<String> clauses = new ArrayList<String>(columnNames.size());
        for (int i = 0; i < columnNames.size(); ++i)
        {
            final String value;
            if (i < values.size())
            {
                value ="'" + values.get(i) + "'";
            }
            else
            {
                value = "?";
            }
            clauses.add(columnNames.get(i) + " = " + value);
        }
        sb = sb + StringUtility.join(clauses, SEP).toString();
           return sb;
    }

    public static String preparePlaceHolders(int length)
    {
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < length;) {
            builder.append("?");
            if (++i < length) {
                builder.append(",");
            }
        }
        return builder.toString();
    }

    public static void setValues(PreparedStatement preparedStatement, Object[] values)
    {
        for (int i = 0; i < values.length; i++)
        {
            try {
                preparedStatement.setObject(i + 1, values[i]);
            } catch (SQLException ex) {
                Logger.getLogger(JDBCHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}
